Release 10.1A: OpenEdge Development:
ProDataSets

Using the COPY-DATASET method for successive FILLs

This second COPY-DATASET example provides a variation on the earlier PickOrder procedure. As it stands, PickOrder.w and its support procedure OrderSupport.p retrieve a set of Order headers, and then in follow-on calls, the OrderLines for those Orders. All the items are retrieved along with the first set of OrderLines. When you select a different set of Orders, the target ProDataSet is emptied and you start over again.

To show a possible use for COPY-DATASET, let’s start by making some changes to the way PickOrder works, which will cause a problem that will then need correcting.

To update your code:

Create a copy of PickOrder.w called PickOrderCopy.w.
Create a variation on dsOrder.i called dsOrderNoRepos.i, which does not have the REPOSITION keyword on the Item relation, as shown:

/* dsOrderNoRepos.i -- include file definition of DATASET dsOrder with no REPOSITION qualifier. */ DEFINE DATASET dsOrder FOR ttOrder, ttOline, ttItem DATA-RELATION OrderLine FOR ttOrder, ttOline RELATION-FIELDS (OrderNum, OrderNum) DATA-RELATION LineItem FOR ttOline, ttItem RELATION-FIELDS (ItemNum, ItemNum).

This will be used in the OrderSupport procedure when it fills the ProDataSet, so that rather than retrieving all Items regardless of their relationship to the OrderLines (which is what REPOSITION does on a FILL), it will retrieve only those Items that match one of the OrderLines being filled at the same time.

Create a copy of OrderSupport.p called OrderSupportCopy.p. Include dsOrderNoRepos.i in place of dsOrder.i, as shown:

/* OrderSupportCopy.p -- FILL events for OrderDset.p */ {dsOrderTT.i} {dsOrderNoRepos.i}

Edit the internal procedure fetchOrderDetail in OrderSupportCopy.p to eliminate the second parameter, which is the flag indicating whether Items have been retrieved already or not. In this variation, you’ll always retrieve Items for the current OrderLines (and only those Items). The FILL-MODE for the ttItem table needs to be changed to MERGE. APPEND mode is correct for the ttOline table, because there won’t be any duplicate OrderLines for a given set of Orders (although MERGE mode would work just as well). MERGE mode is needed for the ttItem table in case the same Item is used in more than one OrderLine. MERGE mode eliminates the duplicates, as shown:

PROCEDURE fetchOrderDetail: DEFINE INPUT PARAMETER piOrderNum AS INTEGER NO-UNDO. /* DEFINE INPUT PARAMETER lFillItems AS LOGICAL NO-UNDO. */ DEFINE OUTPUT PARAMETER DATASET FOR dsOrder BY-VALUE. hDataSet:EMPTY-DATASET. cSelection = "OrderNum = " + STRING(piOrderNum). /* We need to reset the FILL-MODE from its initial setting of NO-FILL for these two tables. */ hDataSet:GET-BUFFER-HANDLE(2):FILL-MODE = "APPEND". /* ttOline */ hDataSet:GET-BUFFER-HANDLE(3):FILL-MODE = "MERGE". /* ttItem */ /* IF lFillItems THEN "APPEND" ELSE "NO-FILL". */ hDataSet:FILL(). END PROCEDURE.

Back in PickOrderCopy.w, change the Main Block to run OrderSupportCopy.p instead of OrderSupport.p
Change the SalesRep LEAVE trigger in PickOrderCopy.w to run the new version of fetchOrders, with only two parameters, as shown:

RUN fetchOrders IN hOrderProc (INPUT cSelection, OUTPUT DATASET dsOrder).

Change the MOUSE-SELECT-DBLCLICK trigger for the ttOrder browse, likewise eliminating the second argument to fetchOrderDetail, as shown:

RUN fetchOrderDetail IN hOrderProc     (INPUT iOrderNum, OUTPUT DATASET dsOrder APPEND).

Now try running the window procedure. Enter 1 for the Customer and tab through the other fill-in fields. You’ll see Orders for Customer 1 as before.
Double-click on the second Order (number 36). You see that only its Items have been retrieved to be displayed in the Item browse:



Now double-click on the next Order, number 79. Its OrderLines and Items are retrieved and added to those already in the window procedure’s ProDataSet. So far so good.
Now double-click on the next Order, number 177. This time you get the following error:



What went wrong? Because the APPEND mode on the ProDataSet parameter in the Order browse trigger doesn’t eliminate duplicates, Progress attempted to add an Item to the window procedure’s ttItem table that was already used in another OrderLine, and was therefore already there. This violates the unique index on the ttItem table, and Progress complains accordingly.

Since you can’t get the APPEND parameter mode to do a merge for you, eliminating duplicates, how can you accomplish this?

One way is to use the COPY-DATASET method to combine the data from two ProDataSets, one which is already on the client, and the other which is retrieved into a separate ProDataSet from the support procedure.

To do this, insert the contents of the include files dsOrderTT.i and dsOrder.i into PickOrderCopy.w’s definitions section, and edit them to give unique names to the second copy of the ProDataSet and its temp-tables. The temp-tables should be named ttOrder2, ttOline2, ttOlineBefore2, and ttItem2. The field and index names can stay the same. The new ProDataSet definition should look like this:

DEFINE DATASET dsOrder2 FOR ttOrder2, ttOline2, ttItem2 DATA-RELATION OrderLine FOR ttOrder2, ttOline2 RELATION-FIELDS (OrderNum, OrderNum) DATA-RELATION LineItem FOR ttOline2, ttItem2 RELATION-FIELDS (ItemNum, ItemNum).

Remember that there is no way to have multiple instances of the same static ProDataSet or temp-table in a single Progress procedure, so the second ProDataSet and its temp-tables need distinct names. You could of course also use a dynamic ProDataSet as the second ProDataSet, as we showed in the previous example.

Now re-edit the MOUSE-SELECT-DBLCLICK trigger on the Order browse to remove the code that empties the target ProDataSet, and to receive the additional Orders, OrderLines, and Items into a second ProDataSet that is then copied into the first one. For example:

/* DELETE ttOrder. FOR EACH ttOline WHERE ttOline.OrderNum = iOrderNum: DELETE ttOline. END. */ RUN fetchOrderDetail IN hOrderProc (INPUT iOrderNum, OUTPUT DATASET dsOrder2). DATASET dsOrder:COPY-DATASET(DATASET dsOrder2:HANDLE, TRUE).

The second argument to COPY-DATASET, the merge-flag TRUE, tells Progress to merge data from ProDataSet dsOrder2 into dsOrder rather than emptying dsOrder first. Now when you rerun the window, retrieve Orders for Customer 1, and select Orders 36, 79, and 177 in turn, the error is gone, because new data is being merged into the original ProDataSet and duplicates automatically eliminated, as shown: